"""
TODO 概述
存储过程：是数据库SQL语言层面的代码封装和重用（类似于java的方法）
特性：输入输出参数
     声明变量
     if/else、case、while等控制语句
     模块化、封装、代码复用
     速度快，只有首次执行需进行编译和优化步骤，后续被调用可直接执行

TODO 语法
delimiter $$
create procedure proc01()
begin
  select ename,sal,deptno from emp;
end $$
delimiter ;                           -- 恢复成分号
call proc01();                        -- 调用函数proc01
drop procedure proc1;                 -- 删除函数proc1


TODO 变量定义
1、局部变量 --范围外无效，只在begin和end之间有效
    delimiter &&
    create procedure proc()
    begin
       declare var_name varchar(20) default 'aaa';   -- 声明/定义变量
       set var_name = '张三';                         -- 给变量赋值
       select var_name;                              -- 输出变量的值
    end  &&
    delimiter ;
    call proc();

   将查询结果赋值给变量：
   delimiter &&
   create procedure proc()
   begin
      declare my_name varchar(20);
      select ename into my_name from emp where deptno = '1001';   -- select..into..（给局部变量赋值）
      select my_name;
   end &&
   delimiter ;
   call proc();

2、用户变量  -- 用户自定义，范围外依然可以调用，当前会话（连接）有效，即两个delimiter之间有效，要求在变量前加@，类似于java的成员变量
   delimiter &&
   create procedure proc()
   begin
      set @my_name = '张三';    -- 不用声明，直接 set 赋值
      select @my_name;
   end &&
   delimiter ;
   call proc()
   select @my_name;

3、系统变量
   ① 全局变量：启动mysql时，服务器自动初始化为默认值，可通过my.ini文件更改
              全局变量的修改会影响整个服务器
       use test1;
       show global variables;         -- 查看所有的全局变量
       select @@global.my_name;       -- 查看某个全局变量

       set @@global.my_name = '张三';  -- 修改全局变量的值
       set global my_name = '张三';

   ② 会话变量：相当于全局变量的copy版
              对其修改，只会影响到当前的会话
      use test1;
      show session variables;         -- 查看所有的会话变量
      select @@session.my_name;       -- 查看某个会话变量
      set @@session.my_name = '张三';   -- 修改会话变量的值
      set session my_name = '张三';

全局变量，由系统提供，在整个数据库中起作用
会话变量在当前会话中起作用

TODO 参数传递
1、传参 - in
   use test1;
   delimiter &&
   create procedure proc(in para_no int)
   begin
      select * from emp where empno = para_no; -- para_no传入后，只能在此使用，无法再传出来
   end &&
   delimiter ;
   call proc(7369);

   封装有参数的存储过程，通过传入部门名和薪资，查询指定部门，且薪资大于指定值的员工信息：
   delimiter &&
   create procedure proc5(in my_name varchar(20),my_sal int)
   begin
      select * from emp join dept on emp.deptno = dept.deptno and dname = my_name and sal > my_sal;
   end &&
   delimiter ;
   call proc5('sales',2500);

2、传参 - out
 封装有参数的存储过程，传入员工编号，返回员工名字
   use test1;
   delimiter &&
   create procedure proc(in my_no int,out out_name varchar(20))
   begin
     select ename into out_name from emp where empno = my_no;
   end &&
   delimiter ;
   call proc(7369,@o_name0);
   select @o_name;

  封装有参数的存储过程，传入员工编号，返回员工名字和薪资
   delimiter &&
   create procedure proc7(in my_no int,out out_name varchar(20),out out_sal double)
   begin
      select ename, sal into out_name, out_sal from emp where empno = my_no;
   end &&
   delimiter ;
   call proc7(7369,@o_name,@o_sal);
   select @o_name,@o_sal;

3、传参 - inout
  传入一个数字，传出这个数字的10倍值
      delimiter &&
      create procedure proc8(inout num int)
      begin
        set num = num *10;
      end &&
      delimiter ;
      set @inout_num = 2;
      call proc8(@inout_num);
      select @inout_num;

  传入员工姓名并拼接部门号，传入薪资，传出年薪
      delimiter &&
      create procedure proc9(inout inout_name varchar(20),inout inout_sal double)
      begin
        select concat_ws('-',deptno, ename) into inout_name from emp where ename = inout_name ;
        set inout_sal = inout_sal *12;
      end &&
      delimiter ;
      set @out_name = 'all', @out_sal = 2000;
      call proc9(@out_name,@out_sal);
      select @out_name,@out_sal;

TODO 流程控制
1、分支语句
 ① if判断
   判断成绩
     delimiter &&
     create procedure proc10(in score int)
     begin
       if score < 60
           then select '不及格';
       elseif score >= 60 and score < 80
           then select '及格';
       elseif score >= 80 and score < 90
           then select '良好';
       elseif score >= 90 and score <= 100
           then select '优秀';
       else
           select '成绩错误';
       end if;
     end &&
     delimiter ;
     set @score = 101;
     call proc10(@score);

   输入员工得名字，判断工资的情况   （0，1000）试用薪资    [1000,2000)转正薪资    [2000,3000)优秀薪资   [3000,+)元老薪资
      delimiter $$
      create procedure proc12(in in_name varchar(20))
      begin
          declare var_sal double;
          declare result varchar(20);
          select sal into var_sal from emp where ename = in_name;
          if var_sal < 1000
             then
               set result = '试用薪资';
          elseif var_sal < 2000
             then
               set result = '转正薪资';
          elseif var_sal <3000
             then
               set result = '优秀薪资';
          else
               set result = '元老薪资';
          end if;
          select result;
      end $$
      delimiter ;
      set @in_name = 'smi';
      call proc12(@in_name);

   ② case判断
      delimiter ^^
      create procedure proc13(in type int)
      begin
         declare result varchar(20);
         case
           when type = 1 then set result = '微信支付';
           when type = 2 then set result = '支付宝支付';
           when type = 3 then set result = '银行卡支付';
           else set result = '其他方式支付';
         end case;
         select result;
      end ^^
      delimiter ;
      call proc13(3);

2、循环语句
  ① while
       use test1;
       create table user(id int,name varchar(20),password varchar(20));
       delimiter %%
       create procedure proc14(in num int)
       begin
          declare i int default 1;
          while i <= num do
               insert into user values(i,concat_ws('-','user',i),'123456');
               set i = i + 1;
          end while;
       end %%
       delimiter ;
       call proc14(8);

  ② while + leave
       truncate table user;
       delimiter %%
       create procedure proc15(in num int)
       begin
         declare i int default 1;
         label:while i <= num do
                   insert into user values(i,concat('user-',i),'123456');
                   if i = 5
                        then leave label;
                   end if;
                   set i = i + 1;
         end while label;
         select '循环结束';
       end %%
       delimiter ;
       call proc15(10);

  ③ while + iterate
         跳过5这一条数据：
          delimiter %%
          create procedure proc15(in num int)
          begin
            declare i int default 0;
            label:while i < num do
                    set i = i + 1;
                    if i = 5
                        then iterate label;
                    end if;
                    insert into user values(i,concat('user-',i),'123456');
            end while label;
          end %%
          delimiter ;
          call proc15(10);

  ④ repeat
        输入10条数据
        truncate table user;
        delimiter &&
        create procedure proc1(in num int)
        begin
           declare i int default 1;
           label:repeat
              insert into user values(i,concat('user-','i'),'123456');
              set i = i + 1;
              until i > num
           end repeat label;
        end &&
        delimiter ;
        call proc1(10)
  ⑤ loop
      输入10条数据
      truncate table user;
      delimiter &&
      create procedure proc2(in num int)
      begin
         declare i int default 1;
         label: loop
             insert into user values(i,concat('user-',i),'123456');
             set i = i + 1;
             if i > num
                then
                   leave label;
             end if;
         end loop label;
      end &&
      delimiter ;
      call proc2(8);
TODO 游标cursor的使用及异常处理（句柄 handler）

游标：对结果集的一个处理，可以给查询的结果添加游标，后续可以对每一行的数据进行处理

创建存储过程的书写顺序：
   声明变量
   声明游标
   声明句柄
定义句柄：即定义异常的处理方式
① 异常处理之后程序怎么执行
   continue：继续执行剩余代码
   exit：    直接终止程序
   undo：    不支持（不用）

② 触发条件
   条件码：
     1329
   条件名：
     SQLWARNING
     NOT FOUND
     SQLEXCEPTION

 ③ 异常触发之后执行什么代码
     设置flag的值为0

     输入一个部门名，查询该部门员工的编号、姓名、薪资，将查询的结果给游标
       delimiter &&
       create procedure proc1(in pro_name varchar(20))
       begin
          declare var_deptno int;                                 -- 定义局部变量
          declare var_dname varchar(20);
          declare var_loc varchar(20);

          declare my_cursor cursor for                            -- 声明游标
               select dept.deptno,dname,loc from emp,dept where emp.deptno = dept.deptno and ename = pro_name;

          open my_cursor;                                         -- 打开游标
          label:loop
             fetch my_cursor into var_deptno,var_dname,var_loc;   -- 通过游标进行取值（fetch一行一行的取值）
             select var_deptno,var_dname,var_loc;
          end loop label;

          close my_cursor;                                        -- 关闭游标
       end &&
       delimiter ;

       call proc1('smi')


TODO 综合练习（报错）
create database mytable;

delimiter &&
create procedure proc2()
begin
 declare next_year int; -- 下一个月的年份
 declare next_month int;-- 下一个月的月份
 declare next_month_day int;-- 下一个月最后一天的日期

 declare next_month_str char(2); -- 下一个月的月份字符串
 declare next_month_day_str char(2);-- 下一个月最后一天的日期字符串

 declare table_name_str char(10);-- 表的名字（每天的表名是变化的）
 declare t_index int default 1; -- 表示天，1为1号，用来标识月份是否小于10（10之后的月份为两位数，1-9月前需加0）

 set next_year = year(date_add(now(),interval 1 month));-- 下一个月的年份
 set next_month = month(date_add(now(), interval 1 month));-- 下一个月的月份
 set next_month_day = dayofmonth(last_day(date_add(now(),interval 1 month))); -- 下一个月最后一天是几号

 if next_month < 10  -- 如果下个月的月份 < 10，前面加0
    then
     set next_month_str = concat('0',next_month);
 else
    set  next_month_str = concat('',next_month);
 end if;

 while t_index <= next_month_day do  -- 循环体：天数 < 下个月的最后一天
   if (t_index < 10)                   -- 天数 < 10，前面加0
      then
       set next_month_day_str = concat('0',t_index);
   else
       set next_month_day_str = concat('',t_index);
   end if;

   set table_name_str = concat(next_year,'-',next_month_str,'-',next_month_day_str);

   set @create_table_sql = concat(
         'create table user_',
         table_name_str,
        '(`uid` int,`uname` varchar(20),`information`,varchar(20)) COLLATE=\'utf8_general_ci\'ENGINE=InnoDB'
   );

   prepare create_table_stmt FROM @create_tabel_sql; -- 预处理（FROM后不能使用局部变量）--开始真正创建表
   execute create_table_stmt; -- 执行
   DEALLOCATE prepare create_table_stmt; -- 释放

   set t_index = t_index + 1;
 end while;
end &&
delimiter ;

call proc2();

TODO 存储函数

     use test1;
     set global log_bin_trust_function_creators = TRUE; --允许创建函数权限信任
     delimiter &&
     create function func() returns int                 -- 创建存储函数，没有参数
     begin
        declare cnt int default 0;                      -- 定义局部变量
        select count(*) into cnt from emp;
        return cnt;
     end &&
     delimiter ;

     select func();                                     -- 调用存储函数

 传入员工的编号，返回员工的名字
     use test1;
     drop function if exists func1;
     set global log_bin_trust_function_creators = TRUE;

     delimiter &&
     create function func1(my_no int) returns varchar(20)
     begin
     declare my_name varchar(20);
       select ename into my_name from emp where empno = my_no;
       return my_name;
     end &&
     delimiter ;

     select func1(7936);

TODO 存储引擎

1、概念：MySQL的核心是存储引擎
2、操作：
    查看所有执行引擎：show engines;
    查看当前默认引擎：show variables like '%storage_engine%';
    创建表时设置引擎：create table student(...) engine = InnoDB;
    修改数据库引擎：  alter table student engine = InnoDB;
    修改默认存储引擎：
           关闭MySQL
           找到MySQL安装目录下的my.ini文件
           找到default_storage_engine = InnoDB
           启动MySQL服务
"""